package com.hotel.repository;

import com.hotel.config.DatabaseConfig;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExtendedRepository {
    public ExtendedRepository() {}

    public Map<String, Object> getDashboardOverview() {
        Map<String, Object> map = new HashMap<>();
        String sql1 = "select count(*) as totalRooms from rooms";
        String sql2 = "select count(*) as availableRooms from rooms where status ='空闲'";
        String sql3 = "select count(*) as occupiedRooms from rooms where status ='已入住'";
        String sql4 = "select sum(total_amount) as todayRevenue from checkins join rooms on rooms.room_id = checkins.room_id where checkout_date IS NOT NULL " +
                "and checkout_date between ? and CURRENT_TIMESTAMP";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt1 = conn.prepareStatement(sql1);
             PreparedStatement stmt2 = conn.prepareStatement(sql2);
             PreparedStatement stmt3 = conn.prepareStatement(sql3);
             PreparedStatement stmt4 = conn.prepareStatement(sql4)) {
            ResultSet rs = stmt1.executeQuery();
            if(rs.next()){
                map.put("totalRooms", rs.getInt("totalRooms"));
            }
            rs = stmt2.executeQuery();
            if(rs.next()){
                map.put("availableRooms", rs.getInt("availableRooms"));
            }
            rs = stmt3.executeQuery();
            if(rs.next()){
                map.put("occupiedRooms", rs.getInt("occupiedRooms"));
            }
            stmt4.setTimestamp(1, Timestamp.valueOf(LocalDate.now().atStartOfDay()));
            rs = stmt4.executeQuery();
            if(rs.next()){
                map.put("todayRevenue", rs.getDouble("todayRevenue"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    public List<Map<String, Object>> getAllRoomStats() {
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "select room_type,count(checkin_id) as total_checkins,sum(total_amount) as total_revenue,AVG(DATEDIFF(checkout_date, checkin_date)) AS avg_stay_days " +
                "from rooms join checkins on rooms.room_id = checkins.room_id " +
                "where checkout_date IS NOT NULL " +
                "group by room_type ";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("room_type", rs.getString("room_type"));
                map.put("total_checkins", rs.getInt("total_checkins"));
                map.put("total_revenue", rs.getDouble("total_revenue"));
                map.put("avg_stay_days", rs.getInt("avg_stay_days"));
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

        public List<Map<String, Object>> getRoomStats(String room_type, String checkin_date, String checkout_date) {
            List<Map<String, Object>> list = new ArrayList<>();
            String sql = "select room_type,count(checkin_id) as total_checkins,sum(total_amount) as total_revenue,AVG(DATEDIFF(checkout_date, checkin_date)) AS avg_stay_days " +
                    "from rooms join checkins on rooms.room_id = checkins.room_id where checkout_date IS NOT NULL and room_type Like ? " +
                    "and checkin_date between ? and ? group by room_type";
            try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
                 PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, "%"+room_type+"%");
                if(!checkin_date.isEmpty()) stmt.setTimestamp(2, Timestamp.valueOf(checkin_date+" 00:00:00"));
                else stmt.setTimestamp(2, Timestamp.valueOf("1001-01-01 00:00:00"));
                if(!checkout_date.isEmpty()) stmt.setTimestamp(3, Timestamp.valueOf(checkout_date+" 23:59:59"));
                else stmt.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
                ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<>();
                    map.put("room_type", rs.getString("room_type"));
                    map.put("total_checkins", rs.getInt("total_checkins"));
                    map.put("total_revenue", rs.getDouble("total_revenue"));
                    map.put("avg_stay_days", rs.getInt("avg_stay_days"));
                    list.add(map);
                }
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
            return list;
    }

    public List<Map<String, Object>> getAllConsumptionStats() {
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "select item_name,sum(quantity) as total_quantity,sum(amount) as total_amount " +
                "from menuitems join consumptions on consumptions.item_id = menuitems.item_id group by item_name";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("item_name", rs.getString("item_name"));
                map.put("total_quantity", rs.getInt("total_quantity"));
                map.put("total_amount", rs.getDouble("total_amount"));
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Map<String, Object>> getConsumptionStats(String item_name, String startDate, String endDate) {
        List<Map<String, Object>> list = new ArrayList<>();
        String sql = "select item_name,sum(quantity) as total_quantity,sum(amount) as total_amount " +
                "from consumptions join menuitems on consumptions.item_id = menuitems.item_id where item_name LIKE ? and consume_time BETWEEN ? AND ? group by item_name";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + item_name + "%");
            if (!startDate.isEmpty()) stmt.setTimestamp(2, Timestamp.valueOf(startDate+" 00:00:00"));
            else stmt.setTimestamp(2, Timestamp.valueOf("1001-01-01 00:00:00"));
            if (!endDate.isEmpty()) stmt.setTimestamp(3, Timestamp.valueOf(endDate+" 23:59:59"));
            else stmt.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("item_name", rs.getString("item_name"));
                map.put("total_quantity", rs.getInt("total_quantity"));
                map.put("total_amount", rs.getDouble("total_amount"));
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }




}
